package org.daochong.lang;

import java.io.File;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.daochong.lang.database.BaseDatabase;
import org.daochong.lang.database.Column;
import org.daochong.lang.database.Database;
import org.daochong.lang.database.MySQL;

@SuppressWarnings("all")
public class JdbcTemplate {

	public static String SHOWSQL_PREFIX = "daochong-jdbc:";
	public static String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
	public static String DEFAULT_NUMBER_FORMAT = null;
	public static String DEFAULT_ENCODING = null;
	public static String DEFAULT_TRUE_STRING = "T";
	public static String DEFAULT_FALSE_STRING = "F";
	public static int DEFAULT_BIGINTEGER_BITS = 10;
	public static String DEFAULT_COLUMN_TYPE = "varchar(50)";
	private static long DEFAULT_CACHE_SECONDS = 60;
	public static Map<String, Database> DATABASES = new HashMap<String, Database>();

	static {
		DATABASES.put("mysql", new MySQL());
		DATABASES.put("oracle", new MySQL());
	}

	private DataSource dataSource;
	private Connection conn = null;
	private boolean tran = false;
	private boolean old = false;
	private Map<String, Object> sqls = null;
	private List<String> sqlBatchs = null;
	private Map<String, Map<String, String>> tables;
	private Map<String, Long> lastAccessTimes;
	private boolean ignoreMappingError = false;
	private boolean ignoreNullValue = false;
	private boolean showSql = false;
	private String productName = null;

	private SQLBuilder builder;
	private boolean useBuilder = false;

	private String dateFormat = DEFAULT_DATE_FORMAT;
	private String numberFormat = DEFAULT_NUMBER_FORMAT;
	private String encoding = DEFAULT_ENCODING;
	private String trueString = DEFAULT_TRUE_STRING;
	private String falseString = DEFAULT_FALSE_STRING;
	private int bigIntegerBits = DEFAULT_BIGINTEGER_BITS;
	private boolean autoCreateColumn;
	private boolean autoCreateTable;
	private boolean autoTransaction = false;
	private boolean debug;

	public JdbcTemplate() {
		init();
	}

	public boolean isDebug() {
		return debug;
	}

	public void setDebug(boolean debug) {
		this.debug = debug;
	}

	public boolean isAutoTransaction() {
		return autoTransaction;
	}

	public void setAutoTransaction(boolean autoTransaction) {
		this.autoTransaction = autoTransaction;
	}

	public JdbcTemplate(DataSource dataSource) {
		this.dataSource = dataSource;
		init();
	}

	public void addSqlBatch(String sql) {
		this.sqlBatchs.add(sql);
	}

	public void addSqlBatch(String sql, List<Object> params) {
		List<Object[]> paramList = (List<Object[]>) this.sqls.get(sql);
		if (paramList == null) {
			paramList = new ArrayList<Object[]>();
			this.sqls.put(sql, paramList);
		}
		paramList.add(params.toArray());
	}

	public void addSqlBatch(String sql, Map<String, Object> params) {
		List<Map<String, Object>> paramList = (List<Map<String, Object>>) this.sqls.get(sql);
		if (paramList == null) {
			paramList = new ArrayList<Map<String, Object>>();
			this.sqls.put(sql, paramList);
		}
		paramList.add(params);
	}

	public void addSqlBatch(String sql, Object... params) {
		List<Object[]> paramList = (List<Object[]>) this.sqls.get(sql);
		if (paramList == null) {
			paramList = new ArrayList<Object[]>();
			this.sqls.put(sql, paramList);
		}
		paramList.add(params);
	}

	public void addSqlBatchMaps(String sql, List<Map<String, Object>> params) {
		List<Map<String, Object>> ps = (List<Map<String, Object>>) this.sqls.get(sql);
		if (ps == null) {
			this.sqls.put(sql, params);
		} else {
			ps.addAll(params);
		}
	}

	public void addSqlBatchValues(String sql, List<Object[]> params) {
		List<Object[]> paramList = (List<Object[]>) this.sqls.get(sql);
		if (paramList == null) {
			this.sqls.put(sql, params);
		} else {
			paramList.addAll(params);
		}

	}

	public void autoCreate(Class<? extends Object> clazz) throws SQLException {
		BeanInfo info = BeanUtil.getOrmInfo(clazz);
		List<String> no = info.getTransients();
		String tableName = info.getTableName().toUpperCase();
		Database database = this.getDatabase();
		String sql = null;
		if (this.isAutoCreateTable() && !database.getTableList(this).contains(tableName)) {
			StringBuilder sb = new StringBuilder();
			sb.append("create table ");
			sb.append(tableName);
			sb.append(" (");
			List<String> ids = info.getIds();
			if (ids.size() > 0) {
				String index = "";
				for (String id : ids) {
					index += id + ",";
					Method m = info.getPropertyGet(id);
					sb.append(BeanUtil.toDatabaseCol(id) + " " + getColumnType(info, id));
					sb.append(" NOT NULL,");
				}
				index = " PRIMARY KEY(" + index.substring(0, index.length() - 1) + ")";
				for (String prop : info.getAllProperties()) {
					if (ids.contains(prop) || no.contains(prop))
						continue;
					sb.append(BeanUtil.toDatabaseCol(prop));
					sb.append(" ");
					sb.append(getColumnType(info, prop));
					sb.append(",");
				}
				sb.append(index + ")");
				sql = sb.toString();
			} else {
				for (String prop : info.getAllProperties()) {
					if (no.contains(prop))
						continue;
					sb.append(BeanUtil.toDatabaseCol(prop));
					sb.append(" ");
					sb.append(database.getColumnType(info.getPropertyGet(prop).getReturnType()));
					sb.append(",");
				}
				sb.append(")");
				sql = sb.toString();
			}
			execute(sql);
			if (this.tables != null) {
				this.tables.remove(tableName);
			}
		}

		if (this.isAutoCreateColumn() || this.isAutoCreateColumn(clazz)) {
			boolean find = false;
			for (String prop : info.getAllProperties()) {
				if (no.contains(prop))
					continue;
				String c = this.getTableColumn(tableName, prop);
				if (c == null) {
					find = true;
					String cc = info.getColumn(prop);
					if (prop.equals(cc)) {
						cc = BeanUtil.toDatabaseCol(cc);
					}
					sql = "alter table " + tableName + " add " + cc + " " + getColumnType(info, prop);
					execute(sql);
				}
			}
			if (find && this.tables != null) {
				this.tables.remove(tableName);
			}
		}
	}

	private String autoCreateColumn(String tableName, String col, Object val) throws SQLException {
		String re = col;
		String sql = "alter table " + tableName.toUpperCase() + " add " + re + " " + getColumnType(val);
		execute(sql);
		if (this.tables != null) {
			this.tables.remove(tableName.toUpperCase());
		}
		return re;
	}

	private void autoCreateTable(String tableName, Map<String, Object> map) throws SQLException {
		if (this.isAutoCreateTable()) {
			if (getDatabase().getTableList(this).contains(tableName.toUpperCase())) {
				return;
			}
			if (map == null || map.size() == 0) {
				throw new RuntimeException("TABLE " + tableName + " must have a column");
			}

			String id = null;
			if (map.containsKey("ID")) {
				id = "ID";
			}
			if (map.containsKey("id")) {
				id = "id";
			}
			if (map.containsKey("Id")) {
				id = "Id";
			}
			if (id == null) {
				for (String key : map.keySet()) {
					if (key.toUpperCase().endsWith("_ID")) {
						id = key;
						break;
					}
				}
			}
			if (id == null) {
				for (String key : map.keySet()) {
					id = key;
					break;
				}
			}
			StringBuilder sb = new StringBuilder();
			sb.append("create table " + tableName + " (" + BeanUtil.toDatabaseCol(id) + " " + getColumnType(map.get(id))
					+ " primary key,");
			for (String key : map.keySet()) {
				if (key.equals(id))
					continue;
				sb.append(BeanUtil.toDatabaseCol(key) + " " + getColumnType(map.get(key)) + ",");
			}
			String sql = sb.toString();
			sql = sql.substring(0, sql.length() - 1) + ")";
			execute(sql);
			if (this.tables != null) {
				this.tables.remove(tableName.toUpperCase());
			}
		}

	}

	public void beginTran() {
		if (this.dataSource == null)
			throw new RuntimeException("dataSource is null");
		try {
			this.conn = this.dataSource.getConnection();
			this.old = conn.getAutoCommit();
			if (old) {
				this.conn.setAutoCommit(false);
			}
			if (this.isDebug()) {
				SystemLogger.info("JdbcTemplate", "Thread[" + Thread.currentThread().getId() + "] Begin Tran");
			}
		} catch (SQLException e) {
			throw new RuntimeException("connection is null", e);
		}
		this.tran = true;
	}

	public void commit() {
		if (!this.isTran())
			return;
		try {
			conn.commit();
			if (this.isDebug()) {
				SystemLogger.info("JdbcTemplate", "Thread[" + Thread.currentThread().getId() + "] Commit");
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public boolean create(Map<String, Object> map, String tableName) throws SQLException {
		if (this.isAutoCreateTable()) {
			autoCreateTable(tableName, map);
		}
		List<Object> params = new ArrayList<Object>();
		String sql = "insert into " + tableName + " (";
		String values = " values(";
		for (String key : map.keySet()) {
			String c = this.getTableColumn(tableName, key);
			if (c == null) {
				if (this.isAutoCreateColumn()) {
					c = this.autoCreateColumn(tableName, key, map.get(key));
				} else if (this.isIgnoreMappingError()) {
					continue;
				} else {
					throw new SQLException("property '" + key + "' is no mapping column");
				}
			}
			Object val = map.get(key);
			if (val == null && this.isIgnoreNullValue()) {
				continue;
			}
			sql += c + ",";
			values += "?,";
			params.add(val);
		}
		if (sql.endsWith(",")) {
			values = values.substring(0, values.length() - 1) + ")";
			sql = sql.substring(0, sql.length() - 1) + ")";
			execute(sql + values, params);
			return true;
		}
		return false;
	}

	public boolean create(Object o) throws SQLException {
		if (o == null)
			return false;
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(o.getClass())
				|| this.isAutoCreateColumn(o.getClass())) {
			autoCreate(o.getClass());
		}
		BeanInfo info = BeanUtil.getOrmInfo(o.getClass());
		Map<String, Object> map = getBeanUtil().pop(o);
		Map<String, Object> data = new HashMap<String, Object>();
		Map<String, String> seqs = info.getSequences();
		boolean isOracle = this.getProductName().equalsIgnoreCase("oracle");
		for (String prop : map.keySet()) {
			Object param = map.get(prop);
			if (param == null)
				continue;
			if (info.canInsert(prop, param)) {
				if (isOracle && seqs.containsKey(prop)) {
					param = this.firstValue("select " + seqs.get(prop) + ".nextval  from dual");
				}
				data.put(info.getColumn(prop), param);
			}
		}
		boolean re = create(data, info.getTableName());

		Method m = info.getPropertySet("extDatas");
		if (m != null) {
			try {
				Object ext = info.getProperty("extDatas", o);
				if (ext != null && (ext instanceof Map)) {

					Map<String, Object> extMap = (Map<String, Object>) ext;
					if (extMap.size() > 0) {
						return updateExtData(o, extMap);
					}
				}
			} catch (Throwable e) {

			}
		}
		return re;
	}

	public boolean create(Object o, Map<String, Object> extData) throws SQLException {
		boolean re = create(o);
		if (extData != null && extData.size() > 0) {
			updateExtData(o, extData);
		}
		return re;
	}

	public String createBeanCode(String tableName, String packageName, String className) {
		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;
		showSql("select * from " + tableName + " where 1=0");
		try {
			if (this.getDataSource() == null)
				throw new RuntimeException("datasource is null");
			cn = this.getDataSource().getConnection();

			List<String> keys = new ArrayList<String>();

			rs = cn.getMetaData().getPrimaryKeys(null, null, tableName);
			while (rs.next()) {
				keys.add(rs.getString("COLUMN_NAME"));
			}
			rs.close();
			st = cn.createStatement();
			rs = st.executeQuery("select * from " + tableName + " where 1=0");
			ResultSetMetaData rmd = rs.getMetaData();
			StringBuffer sb = new StringBuffer();
			if (packageName != null) {
				sb.append("package " + packageName + ";\r\n\r\n");
			}

			List<String> imps = new ArrayList<String>();
			Map<String, String> cols = new LinkedHashMap<String, String>();
			for (int i = 1; i <= rmd.getColumnCount(); i++) {
				String col = rmd.getColumnName(i);
				String cls = rmd.getColumnClassName(i);
				if (cls.startsWith("java.lang.")) {
					cols.put(col, cls.substring(10));
				} else {
					if (cls.equals("[B")) {
						cols.put(col, "byte[]");
					} else {
						int pos = cls.lastIndexOf(".");
						if (!imps.contains(cls))
							imps.add(cls);
						cols.put(col, cls.substring(pos + 1));
					}
				}
			}
			rs.close();

			for (String key : imps) {
				sb.append("import " + key + ";\r\n");
			}
			if (keys.size() > 0) {
				sb.append("import javax.persistence.Id;\r\n");
			}
			sb.append("import javax.persistence.Table;\r\n");
			if (!imps.contains("java.util.Map")) {
				sb.append("import java.util.Map;\r\n");
			}
			sb.append("\r\n@Table(name = \"" + tableName + "\")\r\n");
			sb.append("public class " + (className == null ? BeanUtil.toBeanString(tableName) : className)
					+ "  implements java.io.Serializable {\r\n\r\n\tprivate static final long serialVersionUID = 1L;\r\n\r\n");
			boolean find = false;
			for (String key : cols.keySet()) {
				String prop = BeanUtil.toBeanString(key);
				if (keys.contains(key)) {
					sb.append("\t@Id\r\n");
				}
				if ("extDatas".equals(prop)) {
					find = true;
				}
				sb.append("\tprivate " + cols.get(key) + " " + prop + ";\r\n\r\n");
			}
			if (!find) {
				sb.append("\t@javax.persistence.Transient\r\n\tprivate Map<String,Object> extDatas;\r\n\r\n");
			}
			sb.append("\r\n");
			for (String key : cols.keySet()) {
				String prop = BeanUtil.toBeanString(key);
				sb.append("\tpublic " + cols.get(key) + " get" + StringUtils.firstUpperCase(prop) + "() {\r\n");
				sb.append("\t\treturn this." + prop + ";\r\n");
				sb.append("\t}\r\n\r\n");
				sb.append("\tpublic void set" + StringUtils.firstUpperCase(prop) + "(" + cols.get(key) + " " + prop
						+ ") {\r\n");
				sb.append("\t\tthis." + prop + "=" + prop + ";\r\n");
				sb.append("\t}\r\n\r\n");
			}
			if (!find) {
				sb.append("\tpublic Map<String,Object> getExtDatas() {\r\n");
				sb.append("\t\treturn this.extDatas;\r\n");
				sb.append("\t}\r\n\r\n");
				sb.append("\tpublic void setExtDatas(Map<String,Object> extDatas) {\r\n");
				sb.append("\t\tthis.extDatas=extDatas;\r\n");
				sb.append("\t}\r\n\r\n");
			}
			return sb.toString().trim() + "\r\n}";
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (Throwable e) {

				}
			}
			if (cn != null) {
				try {
					cn.close();
				} catch (Throwable e) {

				}
			}
		}
		return null;
	}

	public void createBeanCode(String tableName, String packageName, String className, String path) {
		try {
			if (className == null) {
				className = BeanUtil.toBeanString(tableName);
			}
			String str = createBeanCode(tableName, packageName, className);
			String fname = className + ".java";
			String filePath = path + "/" + (packageName != null ? packageName.replaceAll("\\.", "/") + "/" : "");
			File file = new File(filePath);
			file.mkdirs();
			FileOutputStream out = new FileOutputStream(filePath + fname);
			out.write(str.getBytes());
			out.close();
		} catch (Throwable e) {
			throw new RuntimeException("createBeanCode Fail", e);
		}
	}

	public boolean delete(Class<?> clazz, Object... keys) throws SQLException {
		if (keys == null || keys.length == 0)
			return false;
		if (keys[0].getClass().equals(clazz)) {
			return delete(keys[0]);
		}
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(clazz)
				|| this.isAutoCreateColumn(clazz)) {
			autoCreate(clazz);
		}
		BeanInfo info = BeanUtil.getOrmInfo(clazz);
		String sql = "delete from " + info.getTableName() + " where ";
		List<Object> params = new ArrayList<Object>();
		int pos = 0;
		for (String key : info.getIds()) {
			String c = getTableColumn(info.getTableName(), info.getColumn(key));
			if (c == null)
				continue;
			if (pos == keys.length)
				break;
			sql += c + " = ? and ";
			params.add(keys[pos++]);
		}
		if (sql.endsWith("and ")) {
			sql = sql.substring(0, sql.length() - 4);
			return execute(sql.trim(), params) > 0;
		}
		return false;
	}

	public boolean delete(Object o) throws SQLException {
		if (o == null)
			return false;
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(o.getClass())
				|| this.isAutoCreateColumn(o.getClass())) {
			autoCreate(o.getClass());
		}
		BeanInfo info = BeanUtil.getOrmInfo(o.getClass());
		String sql = "delete from " + info.getTableName() + " where ";
		List<Object> params = new ArrayList<Object>();
		for (String key : info.getIds()) {
			String c = getTableColumn(info.getTableName(), info.getColumn(key));
			if (c == null)
				continue;
			sql += c + " = ? and ";
			params.add(info.getProperty(key, o));
		}
		if (sql.endsWith("and ")) {
			sql = sql.substring(0, sql.length() - 4);
			return execute(sql.trim(), params) > 0;
		}
		return false;
	}

	public List<TableDiff> diff(DataSource ds) throws SQLException {
		List<TableDiff> list = new ArrayList<TableDiff>();
		JdbcTemplate d = new JdbcTemplate(ds);
		Map<String, Map<String, String>> map = this.getTables();
		Map<String, Map<String, String>> map1 = d.getTables();
		for (String table : map.keySet()) {
			TableDiff td = new TableDiff();
			td.setTableName(table);
			if (!map1.containsKey(table)) {
				td.setOperation(TableDiff.DELETE);
				list.add(td);
				continue;
			}
			Map<String, String> cols = map.get(table);
			Map<String, String> cols1 = map1.get(table);
			for (String col : cols.keySet()) {
				if (cols1.containsKey(col)) {
					if (!cols.get(col).equals(cols1.get(col))) {
						td.setOperation(TableDiff.MODIFY);
						Map<String, List<String>> ml = td.getChangeCols();
						if (ml == null) {
							ml = new LinkedHashMap<String, List<String>>();
							td.setChangeCols(ml);
						}
						List<String> cs = new ArrayList<String>(2);
						cs.add(cols.get(col));
						cs.add(cols1.get(col));
						ml.put(col, cs);
					}
				} else {
					td.setOperation(TableDiff.MODIFY);
					Map<String, String> m = td.getDelCols();
					if (m == null) {
						m = new LinkedHashMap<String, String>();
						td.setDelCols(m);
					}
					m.put(col, cols.get(col));
				}
			}
			for (String col : cols1.keySet()) {
				if (!cols.containsKey(col)) {
					td.setOperation(TableDiff.MODIFY);
					Map<String, String> m = td.getAddCols();
					if (m == null) {
						m = new LinkedHashMap<String, String>();
						td.setAddCols(m);
					}
					m.put(col, cols.get(col));
				}
			}
			if (td.getOperation() != null) {
				list.add(td);
			}
		}
		return list;
	}

	public void endTran() {
		if (!this.isTran())
			return;
		this.tran = false;
		try {
			if (this.conn != null) {
				if (!this.old) {
					conn.setAutoCommit(false);
				}
				this.conn.close();
				this.conn = null;
			}
			if (this.isDebug()) {
				SystemLogger.info("JdbcTemplate", "Thread[" + Thread.currentThread().getId() + "] End Tran");
			}
		} catch (Throwable e) {
			e.printStackTrace();
		}
	}

	public int execute(String sql) throws SQLException {
		if (this.isUseBuilder()) {
			sql = requestSQLBuilder().getSql(sql);
		}
		if (this.isAutoTransaction() && !this.isTran()) {
			this.beginTran();
		}
		showSql(sql);
		Connection cn = null;
		Statement st = null;
		try {
			if (this.isTran()) {
				cn = this.conn;
			} else {
				cn = this.dataSource.getConnection();
			}
			st = cn.createStatement();
			int i = st.executeUpdate(sql);
			return i;
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			throw new SQLException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (Throwable e) {

				}
			}
			if (!this.isTran() && cn != null) {
				try {
					cn.close();
				} catch (Throwable e) {
					e.printStackTrace();
				}
			}
		}
	}

	public int execute(String sql, List<Object> list) throws SQLException {
		return execute(sql, list.toArray());
	}

	public int execute(String sql, Map<String, Object> params) throws SQLException {
		SQLQuery query = getQuery(sql, params);
		return execute(query.getSql(), query.getParams().toArray());
	}

	public int execute(String sql, Object... params) throws SQLException {
		if (params == null || params.length == 0) {
			return execute(sql);
		}
		if (this.isAutoTransaction() && !this.isTran()) {
			this.beginTran();
		}
		Connection cn = null;
		PreparedStatement st = null;
		showSql(sql, params);
		try {
			if (this.isTran()) {
				cn = this.conn;
			} else {
				cn = this.dataSource.getConnection();
			}
			st = cn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				st.setObject(i + 1, params[i]);
			}
			int i = st.executeUpdate();
			return i;
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			throw new SQLException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (Throwable e) {

				}
			}
			if (!this.isTran() && cn != null) {
				try {
					cn.close();
				} catch (Throwable e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void executeBatch() throws SQLException {
		executeBatch(0);
	}

	public void executeBatch(int count) throws SQLException {
		if (!this.isTran()) {
			this.beginTran();
		}
		Statement st = null;
		PreparedStatement ps = null;
		try {
			for (String sql : this.sqls.keySet()) {
				Object o = this.sqls.get(sql);
				if (o == null) {
					throw new SQLException("params is null");
				}
				if (o instanceof List<?>) {
					List<Object> ls = (List<Object>) o;
					if (ls.size() == 0)
						continue;
					if (ls.get(0) instanceof Object[]) {
						List<Object[]> paramList = (List<Object[]>) o;
						if (paramList.size() > 0) {
							int c = 0;
							ps = this.conn.prepareStatement(sql);
							for (Object[] ar : paramList) {
								showSql(sql, ar);
								for (int i = 0; i < ar.length; i++) {
									ps.setObject(i + 1, ar[i]);
								}
								ps.addBatch();
								c++;
								if (count > 0 && c == count) {
									ps.addBatch();
									c = 0;
								}
							}
							if (c > 0) {
								ps.executeBatch();
							}
							ps.close();
						}
					} else if (ls.get(0) instanceof Map) {
						List<Map<String, Object>> lps = (List<Map<String, Object>>) o;
						if (lps.size() > 0) {
							SQLBuilder sb = this.requestSQLBuilder();
							SQLQuery sq = sb.getSql(sql, lps.get(0));
							ps = this.conn.prepareStatement(sq.getSql());
							int c = 0;
							for (Map<String, Object> map : lps) {
								sq = sb.getSql(sql, map);
								showSql(sq.getSql(), sq.getParams().toArray());
								for (int i = 0; i < sq.getParams().size(); i++) {
									ps.setObject(i + 1, sq.getParams().get(i));
								}
								ps.addBatch();
								c++;
								if (count > 0 && c == count) {
									ps.executeBatch();
									c = 0;
								}
							}
							if (c > 0) {
								ps.executeBatch();
							}
							ps.close();
						}
					}
				}
			}
			if (this.sqlBatchs.size() > 0) {
				st = this.conn.createStatement();
				int c = 0;
				for (String sql : this.sqlBatchs) {
					st.addBatch(sql);
					c++;
					if (count > 0 && c == count) {
						st.executeBatch();
						c = 0;
					}
				}
				if (c > 0) {
					st.executeBatch();
				}
			}
			this.commit();
			this.sqls.clear();
		} catch (SQLException e) {
			this.rollback();
			throw e;
		} catch (Exception e) {
			this.rollback();
			throw new SQLException(e);
		} finally {
			this.sqlBatchs.clear();
			this.sqls.clear();
			if (st != null) {
				try {
					st.close();
				} catch (Throwable e) {

				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (Throwable e) {

				}
			}
		}
	}

	public <T> T first(String sql, Class<T> cls) throws SQLException {
		List<T> list = list(sql, cls);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public <T> T first(String sql, Class<T> cls, List<Object> params) throws SQLException {
		List<T> list = list(sql, cls, params.toArray());
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public <T> T first(String sql, Class<T> cls, Map<String, Object> params) throws SQLException {
		List<T> list = list(sql, cls, params);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public <T> T first(String sql, Class<T> cls, Object... params) throws SQLException {
		List<T> list = list(sql, cls, params);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public int firstIntValue(String sql) throws SQLException {
		try {
			return Integer.parseInt(firstStringValue(sql));
		} catch (Throwable e) {
			return 0;
		}
	}

	public int firstIntValue(String sql, List<Object> params) throws SQLException {
		try {
			return Integer.parseInt(firstStringValue(sql, params.toArray()));
		} catch (Throwable e) {
			return 0;
		}
	}

	public int firstIntValue(String sql, Map<String, Object> params) throws SQLException {
		try {
			return Integer.parseInt(firstStringValue(sql, params));
		} catch (Throwable e) {
			return 0;
		}
	}

	public int firstIntValue(String sql, Object... params) {
		try {
			return Integer.parseInt(firstStringValue(sql, params));
		} catch (Throwable e) {
			return 0;
		}
	}

	public Map<String, Object> firstMap(String sql) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public Map<String, Object> firstMap(String sql, List<Object> params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params.toArray());
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public Map<String, Object> firstMap(String sql, Map<String, Object> params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public Map<String, Object> firstMap(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public Map<String, String> firstString(String sql) throws SQLException {
		List<Map<String, String>> list = listString(sql);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public Map<String, String> firstString(String sql, List<Object> params) throws SQLException {
		List<Map<String, String>> list = listString(sql, params.toArray());
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public Map<String, String> firstString(String sql, Map<String, Object> params) throws SQLException {
		List<Map<String, String>> list = listString(sql, params);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public Map<String, String> firstString(String sql, Object... params) throws SQLException {
		List<Map<String, String>> list = listString(sql, params);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public String firstStringValue(String sql) throws SQLException {
		List<Map<String, String>> list = listString(sql);
		if (list != null && list.size() > 0) {
			Map<String, String> map = list.get(0);
			for (String key : map.keySet()) {
				return map.get(key);
			}
		}
		return null;
	}

	public String firstStringValue(String sql, List<Object> params) throws SQLException {
		List<Map<String, String>> list = listString(sql, params);
		if (list != null && list.size() > 0) {
			Map<String, String> map = list.get(0);
			for (String key : map.keySet()) {
				return map.get(key);
			}
		}
		return null;
	}

	public String firstStringValue(String sql, Map<String, Object> params) throws SQLException {
		List<Map<String, String>> list = listString(sql, params);
		if (list != null && list.size() > 0) {
			Map<String, String> map = list.get(0);
			for (String key : map.keySet()) {
				return map.get(key);
			}
		}
		return null;
	}

	public String firstStringValue(String sql, Object... params) throws SQLException {
		List<Map<String, String>> list = listString(sql, params);
		if (list != null && list.size() > 0) {
			Map<String, String> map = list.get(0);
			for (String key : map.keySet()) {
				return map.get(key);
			}
		}
		return null;
	}

	public Object firstValue(String sql) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql);
		if (list != null && list.size() > 0) {
			Map<String, Object> map = list.get(0);
			for (String key : map.keySet()) {
				return map.get(key);
			}
		}
		return null;
	}

	public Object firstValue(String sql, List<Object> params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params.toArray());
		if (list != null && list.size() > 0) {
			Map<String, Object> map = list.get(0);
			for (String key : map.keySet()) {
				return map.get(key);
			}
		}
		return null;
	}

	public Object firstValue(String sql, Map<String, Object> params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params);
		if (list != null && list.size() > 0) {
			Map<String, Object> map = list.get(0);
			for (String key : map.keySet()) {
				return map.get(key);
			}
		}
		return null;
	}

	public Object firstValue(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params);
		if (list != null && list.size() > 0) {
			Map<String, Object> map = list.get(0);
			for (String key : map.keySet()) {
				return map.get(key);
			}
		}
		return null;
	}

	private BeanUtil getBeanUtil() {
		BeanUtil beanUtil = new BeanUtil();
		if (this.getDateFormat() == null) {
			beanUtil.setDateFormat(this.getDateFormat());
		}
		if (this.getNumberFormat() != null) {
			beanUtil.setNumberFormat(new DecimalFormat(this.getNumberFormat()));
		}
		beanUtil.setBigIntegerBits(getBigIntegerBits());
		beanUtil.setEncoding(this.getEncoding());
		beanUtil.setFalseString(this.getFalseString());
		beanUtil.setTrueString(this.getTrueString());
		return beanUtil;
	}

	public int getBigIntegerBits() {
		return bigIntegerBits;
	}

	public SQLBuilder getBuilder() {
		return builder;
	}

	private String getColumnType(BeanInfo info, String prop) {
		Method m = info.getPropertyGet(prop);
		if (m != null) {
			int length = (info.getLengths().containsKey(prop) ? info.getLengths().get(prop) : 0);
			int pre = (info.getPrecisions().containsKey(prop) ? info.getPrecisions().get(prop) : 0);
			return getDatabase().getColumnType(m.getReturnType(), length, pre);
		}
		Field field = info.getField(prop);
		if (field != null) {
			int length = (info.getLengths().containsKey(prop) ? info.getLengths().get(prop) : 0);
			int pre = (info.getPrecisions().containsKey(prop) ? info.getPrecisions().get(prop) : 0);
			return getDatabase().getColumnType(field.getType(), length, pre);
		}
		throw new RuntimeException("BeanProperty " + prop + " is not exists");
	}

	private String getColumnType(Object val) {
		if (val == null) {
			if ("oracle".equalsIgnoreCase(this.getProductName())) {
				return DEFAULT_COLUMN_TYPE.replace("varchar", "varchar2");
			}
			return DEFAULT_COLUMN_TYPE;
		}
		return getDatabase().getColumnType(val);
	}

	private Database getDatabase() {
		Database database = DATABASES.get(this.getProductName().toLowerCase());
		if (database != null) {
			return database;
		} else {
			return new BaseDatabase();
		}
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public String getDateFormat() {
		return dateFormat;
	}

	public String getEncoding() {
		return encoding;
	}

	public String getFalseString() {
		return falseString;
	}

	public String getNumberFormat() {
		return numberFormat;
	}

	public String getProductName() {
		if (productName == null) {
			try {
				Connection cn = this.getDataSource().getConnection();
				productName = cn.getMetaData().getDatabaseProductName();
				cn.close();
			} catch (Throwable e) {

			}
		}
		return productName;
	}

	public int getTableId(Object o) throws SQLException {
		return getTableId(o.getClass());
	}

	public int getTableId(Class<?> clazz) throws SQLException {
		BeanInfo info = BeanUtil.getOrmInfo(clazz);
		return getTableId(info.getTableName(), info.getIds().get(0));
	}

	public int getTableId(String tableName, String col) throws SQLException {
		int re = this.firstIntValue("select max(" + col + ") c from " + tableName);
		if (re == 0)
			return 1;
		return re;
	}

	private SQLQuery getQuery(String sql, Map<String, Object> params) {
		SQLBuilder builder = requestSQLBuilder();
		if (params == null) {
			params = new HashMap<String, Object>();
		}
		for (String p : builder.getSqlParams(sql)) {
			if (!params.containsKey(p)) {
				params.put(p, null);
			}
		}
		return builder.getSql(sql, params);
	}

	private Map<String, String> getTableCols(String tableName) throws SQLException {
		Map<String, String> cols = this.tables.get(tableName.toUpperCase());
		if (cols == null || (this.lastAccessTimes.containsKey(tableName.toUpperCase()) && (System.currentTimeMillis()
				- this.lastAccessTimes.get(tableName.toUpperCase())) > DEFAULT_CACHE_SECONDS * 1000)) {
			cols = new LinkedHashMap<String, String>();
			Map<String, Column> maps = getDatabase().getColumns(tableName, this);
			for (Column column : maps.values()) {
				String value = column.getType();
				if (column.getLength() > 0) {
					value += ":" + column.getLength();
				}
				cols.put(column.getName().toUpperCase(), value);
			}
			this.tables.put(tableName.toUpperCase(), cols);
			this.lastAccessTimes.put(tableName.toUpperCase(), System.currentTimeMillis());
		}
		return cols;
	}

	public String getTableColumn(String tableName, String col) throws SQLException {
		Map<String, String> cols = getTableCols(tableName);
		for (String c : cols.keySet()) {
			if (c.equalsIgnoreCase(col) || c.replaceAll("\\_", "").equalsIgnoreCase(col)) {
				return c;
			}
		}
		return null;
	}

	public Map<String, Map<String, String>> getTables() throws SQLException {
		this.tables = new LinkedHashMap<String, Map<String, String>>();
		List<String> ts = getDatabase().getTableList(this);
		for (int i = 0; i < ts.size(); i++) {
			ts.set(i, ts.get(i).toUpperCase());
			getTableCols(ts.get(i));
		}
		this.lastAccessTimes.clear();
		return this.tables;
	}

	public String getTrueString() {
		return trueString;
	}

	private void init() {
		this.sqls = new LinkedHashMap<String, Object>();
		this.sqlBatchs = new ArrayList<String>();
		this.tables = new LinkedHashMap<String, Map<String, String>>();
		this.lastAccessTimes = new HashMap<String, Long>();
	}

	public boolean isAutoCreateColumn() {
		return autoCreateColumn;
	}

	public boolean isAutoCreateColumn(Class<?> clazz) {
		return BeanUtil.getOrmInfo(clazz).isAutoCreate();
	}

	public boolean isAutoCreateTable() {
		return autoCreateTable;
	}

	public boolean isAutoCreateTable(Class<?> clazz) {
		return BeanUtil.getOrmInfo(clazz).isAutoCreate();
	}

	public boolean isExists(Class<?> clazz, Object... keys) throws SQLException {
		if (keys == null || keys.length == 0)
			return false;
		if (keys[0].getClass().equals(clazz)) {
			return delete(keys[0]);
		}
		BeanInfo info = BeanUtil.getOrmInfo(clazz);
		String sql = "select count(*) c from " + info.getTableName() + " where ";
		List<Object> params = new ArrayList<Object>();
		int pos = 0;
		for (String key : info.getIds()) {
			String c = getTableColumn(info.getTableName(), info.getColumn(key));
			if (c == null)
				continue;
			if (pos == keys.length)
				break;
			sql += c + " = ? and ";
			params.add(keys[pos++]);
		}
		if (sql.endsWith("and ")) {
			sql = sql.substring(0, sql.length() - 4);
			int re = firstIntValue(sql.trim(), params);

			return re > 0;
		}
		return false;
	}

	public boolean isExists(Object o) throws SQLException {
		if (o == null)
			return false;
		BeanInfo info = BeanUtil.getOrmInfo(o.getClass());
		String sql = "select count(*) c from " + info.getTableName() + " where ";
		List<Object> params = new ArrayList<Object>();
		for (String key : info.getIds()) {
			String c = getTableColumn(info.getTableName(), info.getColumn(key));
			if (c == null)
				continue;
			sql += c + " = ? and ";
			params.add(info.getProperty(key, o));
		}
		if (sql.endsWith("and ")) {
			sql = sql.substring(0, sql.length() - 4);
			int re = firstIntValue(sql.trim(), params);

			return re > 0;
		}
		return false;
	}

	public boolean isIgnoreMappingError() {
		return ignoreMappingError;
	}

	public boolean isIgnoreNullValue() {
		return ignoreNullValue;
	}

	public boolean isShowSql() {
		return showSql;
	}

	public boolean isTran() {
		return tran;
	}

	public boolean isUseBuilder() {
		return useBuilder;
	}

	public <T> PageResult<T> list(Page page, String sql, Class<T> cls) throws SQLException {
		return list(page, sql, cls, new Object[0]);
	}

	public <T> PageResult<T> list(Page page, String sql, Class<T> cls, List<Object> params) throws SQLException {
		return list(page, sql, cls, params.toArray());
	}

	public <T> PageResult<T> list(Page page, String sql, Class<T> cls, Map<String, Object> params) throws SQLException {
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(cls)
				|| this.isAutoCreateColumn(cls)) {
			autoCreate(cls);
		}
		SQLQuery query = getQuery(sql, params);
		PageResult<Map<String, Object>> list = listObjects(page, query.getSql(), query.getParams().toArray());
		PageResult<T> result = new PageResult<T>();
		result.setCount(list.getCount());
		result.setPage(list.getPage());
		result.setPageSize(list.getPageSize());
		List<T> data = new ArrayList<T>();
		result.setData(data);
		if (list.getCount() > 0) {
			data.addAll(getBeanUtil().push(list.getData(), cls));
		}
		return result;
	}

	public <T> PageResult<T> list(Page page, String sql, Class<T> cls, Object... params) throws SQLException {
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(cls)
				|| this.isAutoCreateColumn(cls)) {
			autoCreate(cls);
		}
		PageResult<Map<String, Object>> list = listObjects(page, sql, params);
		PageResult<T> result = new PageResult<T>();
		result.setCount(list.getCount());
		result.setPage(list.getPage());
		result.setPageSize(list.getPageSize());
		List<T> data = new ArrayList<T>();
		result.setData(data);
		if (list.getCount() > 0) {
			data.addAll(getBeanUtil().push(list.getData(), cls));
		}
		return result;
	}

	public <T> List<T> list(String sql, Class<T> cls) throws SQLException {
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(cls)
				|| this.isAutoCreateColumn(cls)) {
			autoCreate(cls);
		}
		List<Map<String, Object>> list = listObjects(sql);
		return getBeanUtil().push(list, cls);
	}

	public <T> List<T> list(String sql, Class<T> cls, List<Object> params) throws SQLException {
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(cls)
				|| this.isAutoCreateColumn(cls)) {
			autoCreate(cls);
		}
		List<Map<String, Object>> list = listObjects(sql, params.toArray());
		return getBeanUtil().push(list, cls);
	}

	public <T> List<T> list(String sql, Class<T> cls, Map<String, Object> params) throws SQLException {
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(cls)
				|| this.isAutoCreateColumn(cls)) {
			autoCreate(cls);
		}
		List<Map<String, Object>> list = listObjects(sql, params);
		return getBeanUtil().push(list, cls);
	}

	public <T> List<T> list(String sql, Class<T> cls, Object... params) throws SQLException {
		if (this.isAutoCreateColumn() || this.isAutoCreateTable()) {
			autoCreate(cls);
		}
		List<Map<String, Object>> list = listObjects(sql, params);
		return getBeanUtil().push(list, cls);
	}

	public PageResult<Map<String, Object>> listObjects(Page page, String sql) throws SQLException {
		return listObjects(page, sql, new Object[] {});
	}

	public PageResult<Map<String, Object>> listObjects(Page page, String sql, List<Object> params) throws SQLException {
		return listObjects(page, sql, params.toArray());
	}

	public PageResult<Map<String, Object>> listObjects(Page page, String sql, Map<String, Object> params)
			throws SQLException {
		SQLQuery query = getQuery(sql, params);
		return listObjects(page, query.getSql(), query.getParams().toArray());
	}

	public PageResult<Map<String, Object>> listObjects(Page page, String sql, Object... params) throws SQLException {
		Database database = DATABASES.get(this.getProductName().toLowerCase());
		if (database != null) {
			return database.listObjects(this, page, sql, params);
		}
		PageResult<Map<String, Object>> result = new PageResult<Map<String, Object>>();
		result.setPage(page.getPage());
		result.setPageSize(page.getPageSize());
		if (result.getPage() <= 0) {
			result.setPage(1);
		}
		if (result.getPageSize() <= 0) {
			result.setPageSize(20);
		}
		List<Map<String, Object>> list = this.listObjects(sql, params);
		if (list != null && list.size() > 0) {
			result.setCount(list.size());
			list = list.subList((result.getPage() - 1) * result.getPageSize(), result.getPage() * result.getPageSize());
			result.setData(list);
		}
		return result;
	}

	private List<Map<String, Object>> listObjects(ResultSet rs) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			ResultSetMetaData cols = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				list.add(map);
				for (int i = 1; i <= cols.getColumnCount(); i++) {
					String col = cols.getColumnLabel(i);
					Object o = null;
					try {
						o = rs.getObject(i);
					} catch (SQLException e) {

					}
					map.put(col, o);
				}
			}
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Throwable e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public List<Map<String, Object>> listObjects(String sql) throws SQLException {
		if (this.isUseBuilder()) {
			sql = requestSQLBuilder().getSql(sql);
		}
		showSql(sql);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			cn = dataSource.getConnection();
			st = cn.createStatement();
			rs = st.executeQuery(sql);
			list = listObjects(rs);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Throwable e) {
				e.printStackTrace();
			}
			try {
				if (st != null)
					st.close();
			} catch (Throwable e) {
				e.printStackTrace();
			}
			try {
				if (cn != null)
					cn.close();
			} catch (Throwable e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public List<Map<String, Object>> listObjects(String sql, List<Object> list) throws SQLException {
		return listObjects(sql, list.toArray());
	}

	public List<Map<String, Object>> listObjects(String sql, Map<String, Object> params) throws SQLException {
		SQLQuery query = getQuery(sql, params);
		return listObjects(query.getSql(), query.getParams().toArray());
	}

	public List<Map<String, Object>> listObjects(String sql, Object... params) throws SQLException {
		if (params == null || params.length == 0) {
			return listObjects(sql);
		}
		showSql(sql, params);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Connection cn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			cn = dataSource.getConnection();
			st = cn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				st.setObject(i + 1, params[i]);
			}
			rs = st.executeQuery();
			list = listObjects(rs);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Throwable e) {
				e.printStackTrace();
			}
			try {
				if (st != null)
					st.close();
			} catch (Throwable e) {
				e.printStackTrace();
			}
			try {
				if (cn != null)
					cn.close();
			} catch (Throwable e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	private List<Map<String, String>> listString(List<Map<String, Object>> list) {
		List<Map<String, String>> re = new ArrayList<Map<String, String>>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for (Map<String, Object> map : list) {
			Map<String, String> m = new LinkedHashMap<String, String>();
			for (String key : map.keySet()) {
				Object o = map.get(key);
				if (o == null) {
					m.put(key, "");
				} else if (o instanceof String) {
					m.put(key, (String) o);
				} else if (o instanceof byte[]) {
					m.put(key, new String((byte[]) o));
				} else if (o instanceof char[]) {
					m.put(key, new String((char[]) o));
				} else if (o instanceof java.sql.Date) {
					m.put(key, sdf.format((java.sql.Date) o));
				} else if (o instanceof java.sql.Timestamp) {
					m.put(key, sdf.format((java.sql.Timestamp) o));
				} else {
					m.put(key, o.toString());
				}
			}
			re.add(m);
		}
		return re;
	}

	public List<Map<String, String>> listString(String sql) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql);
		return listString(list);
	}

	public List<Map<String, String>> listString(String sql, List<Object> params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params.toArray());
		return listString(list);
	}

	public List<Map<String, String>> listString(String sql, Map<String, Object> params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params);
		return listString(list);
	}

	public List<Map<String, String>> listString(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> list = listObjects(sql, params);
		return listString(list);
	}

	public <T> T load(Class<T> clazz, Map<String, Object> keys) throws SQLException {
		BeanInfo info = new BeanInfo(clazz);
		String tableName = info.getTableName();
		String sql = "select * from " + tableName + " where ";
		List<Object> params = new ArrayList<Object>();
		for (String key : info.getIds()) {
			sql += getTableColumn(tableName, key) + " = ? and ";
			params.add(keys.get(key));
		}
		if (sql.endsWith(" where ")) {
			return first("select * from " + tableName, clazz);
		} else {
			sql = sql.substring(0, sql.length() - 4);
			return first(sql, clazz, params);
		}
	}

	public <T> T load(Class<T> clazz, Object... params) throws SQLException {
		BeanInfo info = new BeanInfo(clazz);
		String tableName = info.getTableName();
		String sql = "select * from " + tableName + " where ";
		for (String key : info.getIds()) {
			String c = this.getTableColumn(tableName, info.getColumn(key));
			if (c == null)
				continue;
			sql += c + " = ? and ";
		}
		if (sql.endsWith(" where ")) {
			return (T) first("select * from " + tableName, clazz);
		} else {
			return (T) first(sql.substring(0, sql.length() - 4), clazz, params);
		}
	}

	public <T> T load(T obj) throws SQLException {
		BeanInfo info = new BeanInfo(obj.getClass());
		String tableName = info.getTableName();
		String sql = "select * from " + tableName + " where ";
		List<Object> params = new ArrayList<Object>();
		for (String key : info.getIds()) {
			String c = this.getTableColumn(tableName, info.getColumn(key));
			if (c == null)
				continue;
			sql += c + " = ? and ";
			params.add(info.getProperty(key, obj));
		}
		if (sql.endsWith(" where ")) {
			return (T) first("select * from " + tableName, obj.getClass());
		} else {
			return (T) first(sql.substring(0, sql.length() - 4), obj.getClass(), params);
		}
	}

	public <T> List<T> loadAll(Class<T> clazz) throws SQLException {
		BeanInfo info = new BeanInfo(clazz);
		String tableName = info.getTableName();
		String sql = "select * from " + tableName;
		return list(sql, clazz);
	}

	public boolean locked(Object o) throws SQLException {
		if (o == null)
			return false;
		BeanInfo info = new BeanInfo(o.getClass());
		String tableName = info.getTableName();
		String sql = "select * from " + tableName + " where ";
		List<Object> params = new ArrayList<Object>();
		for (String key : info.getIds()) {
			sql += getTableColumn(tableName, key) + " = ? and ";
			params.add(info.getProperty(key, o));
		}
		if (sql.endsWith(" where ")) {
			return false;
		} else {
			sql = sql.substring(0, sql.length() - 4) + " for update";
			return first(sql, o.getClass(), params) != null;
		}
	}

	public void register(Class<?> clazz) {
		BeanUtil.getOrmInfo(clazz);
	}

	public void register(Class<?> clazz, String tableName, Map<String, String> colMapping, List<String> ids,
			List<String> transients) {
		BeanInfo info = BeanUtil.getOrmInfo(clazz);
		if (StringUtils.isEmpty(tableName)) {
			info.setTableName(tableName);
		}
		info.setColMapping(colMapping);
		info.setIds(ids);
		info.setTransients(transients);
	}

	public SQLBuilder requestSQLBuilder() {
		SQLBuilder builder = getBuilder();
		if (builder == null) {
			return SQLBuilder.getInstance();
		}
		return builder;
	}

	public void rollback() {
		if (!this.isTran())
			return;
		try {
			conn.rollback();
			if (this.isDebug()) {
				SystemLogger.info("JdbcTemplate", "Thread[" + Thread.currentThread().getId() + "] Rollback");
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public boolean save(Object o) throws SQLException {
		if (o == null)
			return false;
		BeanInfo info = BeanUtil.getOrmInfo(o.getClass());
		if (info.getIds() == null || info.getIds().size() == 0) {
			return create(o);
		} else if (isExists(o)) {
			return update(o);
		} else {
			return create(o);
		}
	}

	public boolean save(Object o, Map<String, Object> extData) throws SQLException {
		if (o == null)
			return false;
		BeanInfo info = BeanUtil.getOrmInfo(o.getClass());
		if (info.getIds() == null || info.getIds().size() == 0) {
			return create(o, extData);
		} else if (isExists(o)) {
			return update(o, extData);
		} else {
			return create(o, extData);
		}
	}

	public void setAutoCreateColumn(boolean autoCreateColumn) {
		this.autoCreateColumn = autoCreateColumn;
	}

	public void setAutoCreateTable(boolean autoCreateTable) {
		this.autoCreateTable = autoCreateTable;
	}

	public void setBigIntegerBits(int bigIntegerBits) {
		this.bigIntegerBits = bigIntegerBits;
	}

	public void setBuilder(SQLBuilder builder) {
		this.builder = builder;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		init();
	}

	public void setDateFormat(String dateFormat) {
		this.dateFormat = dateFormat;
	}

	public void setEncoding(String encoding) {
		this.encoding = encoding;
	}

	public void setFalseString(String falseString) {
		this.falseString = falseString;
	}

	public void setIgnoreMappingError(boolean ignoreMappingError) {
		this.ignoreMappingError = ignoreMappingError;
	}

	public void setIgnoreNullValue(boolean ignoreNullValue) {
		this.ignoreNullValue = ignoreNullValue;
	}

	public void setNumberFormat(String numberFormat) {
		this.numberFormat = numberFormat;
	}

	public void setShowSql(boolean showSql) {
		this.showSql = showSql;
	}

	public void setTrueString(String trueString) {
		this.trueString = trueString;
	}

	public void setUseBuilder(boolean useBuilder) {
		this.useBuilder = useBuilder;
	}

	private void showSql(String sql) {
		showSql(sql, new Object[0]);
	}

	private void showSql(String sql, Object... params) {
		if (this.isShowSql()) {
			StackTraceElement caller = BeanUtil.getCallerInfo();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			System.out.println(sdf.format(new Date()) + " " + caller.getClassName() + " " + caller.getMethodName()
					+ "(line:" + caller.getLineNumber() + ")");
			System.out.println(SHOWSQL_PREFIX + (sql));
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					System.out.println("Param" + (i + 1) + ":" + params[i]);
				}
			}
		}
	}

	public boolean update(Map<String, Object> data, String tableName, List<String> keys) throws SQLException {
		return update(data, tableName, keys.toArray(new String[keys.size()]));
	}

	public boolean update(Map<String, Object> data, String tableName, boolean autoCreateColumn, List<String> keys)
			throws SQLException {
		return update(data, tableName, true, keys.toArray(new String[keys.size()]));
	}

	public boolean update(Map<String, Object> data, String tableName, String... keys) throws SQLException {
		return update(data, tableName, false, keys);
	}

	public boolean update(Map<String, Object> data, String tableName, boolean autoCreateColumn, String... keys)
			throws SQLException {
		if (keys == null || keys.length == 0)
			return false;
		List<Object> params = new ArrayList<Object>();
		List<Object> paramsKeys = new ArrayList<Object>();
		String sql = "update " + tableName + " set ";
		String where = " where ";
		for (String key : keys) {
			if (!data.containsKey(key))
				continue;
			String c = getTableColumn(tableName, key);
			if (c == null) {
				if (autoCreateColumn || this.isAutoCreateColumn()) {
					c = this.autoCreateColumn(tableName, key, data.get(key));
				} else if (this.isIgnoreMappingError()) {
					data.remove(key);
					continue;
				} else {
					throw new SQLException("property '" + key + "' is no mapping column");
				}
			}
			Object val = data.get(key);
			if (val == null && this.isIgnoreNullValue()) {
				continue;
			}
			where += c + " = ? and ";
			paramsKeys.add(val);
			data.remove(key);
		}
		for (String key : data.keySet()) {
			String c = getTableColumn(tableName, key);
			if (c == null) {
				if (this.isAutoCreateColumn() || autoCreateColumn) {
					c = this.autoCreateColumn(tableName, key, data.get(key));
				} else if (this.isIgnoreMappingError()) {
					continue;
				} else {
					throw new SQLException("property '" + key + "' is no mapping column");
				}
			}
			Object val = data.get(key);
			if (val == null && this.isIgnoreNullValue()) {
				continue;
			}
			sql += c + " = ? ,";
			params.add(val);
		}
		params.addAll(paramsKeys);
		if (sql.endsWith(",") && where.endsWith("and ")) {
			where = where.substring(0, where.length() - 4);
			sql = sql.substring(0, sql.length() - 1);
			execute(sql + where, params);
			return true;
		}
		return false;
	}

	public boolean update(Object o) throws SQLException {
		if (o == null)
			return false;
		if (this.isAutoCreateColumn() || this.isAutoCreateTable() || this.isAutoCreateTable(o.getClass())
				|| this.isAutoCreateColumn(o.getClass())) {
			autoCreate(o.getClass());
		}
		BeanInfo info = BeanUtil.getOrmInfo(o.getClass());
		Map<String, Object> map = getBeanUtil().pop(o);
		Map<String, Object> data = new HashMap<String, Object>();
		for (String prop : map.keySet()) {
			Object param = map.get(prop);
			if (info.canUpdate(prop, param)) {
				data.put(info.getColumn(prop), param);
			}
		}
		List<String> keys = new ArrayList<String>();
		for (String key : info.getIds()) {
			keys.add(info.getColumn(key));
		}

		boolean re = update(data, info.getTableName(), keys.toArray(new String[keys.size()]));
		Method m = info.getPropertySet("extDatas");
		if (m != null) {
			try {
				Object ext = info.getProperty("extDatas", o);
				if (ext != null && (ext instanceof Map)) {

					Map<String, Object> extMap = (Map<String, Object>) ext;
					if (extMap.size() > 0) {
						return updateExtData(o, extMap);
					}
				}
			} catch (Throwable e) {

			}
		}
		return re;
	}

	public boolean update(Object o, Map<String, Object> extData) throws SQLException {
		boolean re = update(o);
		if (extData != null && extData.size() > 0) {
			return updateExtData(o, extData);
		}
		return re;
	}

	public boolean updateWithoutNull(Object o, Map<String, Object> extData) throws SQLException {
		boolean re = updateWithoutNull(o);
		if (extData != null && extData.size() > 0) {
			return updateExtData(o, extData);
		}
		return re;
	}

	public boolean updateExtData(Object o, Map<String, Object> extData) throws SQLException {
		BeanInfo info = BeanUtil.getOrmInfo(o.getClass());
		List<String> ids = info.getIds();
		for (String id : ids) {
			extData.put(id, info.getProperty(id, o));
		}
		return update(extData, info.getTableName(), true, ids);
	}

	public boolean updateWithoutNull(Object o) throws SQLException {
		if (o == null)
			return false;
		BeanInfo info = BeanUtil.getOrmInfo(o.getClass());
		Map<String, Object> map = getBeanUtil().pop(o);
		Map<String, Object> data = new HashMap<String, Object>();
		for (String prop : map.keySet()) {
			Object param = map.get(prop);
			if (param != null && info.canUpdate(prop, param)) {
				data.put(info.getColumn(prop), param);
			}
		}
		List<String> keys = new ArrayList<String>();
		for (String key : info.getIds()) {
			keys.add(info.getColumn(key));
		}
		return update(data, info.getTableName(), keys.toArray(new String[keys.size()]));
	}
}
